CREATE DATABASE RecrutimentStore
GO
USE RecrutimentStore
GO
CREATE TABLE Departments
(
	DepartmentID varchar(5) primary key not null,
	DepartmentName varchar(30) not null,
	DeStatus int not null
)

CREATE TABLE Employees
(
	EmpNo varchar(30) primary key not null,
	DepartmentID varchar(5) foreign key references Departments(DepartmentID) not null,
	EmpName varchar(50), --- name of the Employee or interviewer
	Password varchar(30) not null,
	Address varchar(50),
	Phone varchar(15),
	Email varchar(30),	
	EmStatus int not null,
)

GO


CREATE TABLE Vacances
(
	VacancyID varchar(5) primary key not null,
	DepartmentID varchar(5) foreign key references Departments(DepartmentID) not null, 
	VacantyTitle varchar(100)not null ,
	NumOfJob int not null,
	Salary varchar(10) not null,
	Experience varchar(10) not null,
	StartDate datetime default getdate(), --defaut as today
	CloseDate datetime,
	VaStatus int not null --Open/Close/Suspended
)

--insert into Vacances values('V0001','Java Dep',default,default,null,'Open')
--delete from Interviews

CREATE TABLE Applicants
(
	ApplicantID varchar(5) primary key not null,	
	ApplicantName varchar(50) not null,
	AppPhone varchar(20) not null,
	AppEmail varchar(30) not null,
	AppAbout text not null,
	DOC datetime default getdate(), -- Date of create default as today	
	AppStatus int not null --Not in Process/In Process/Hired/Banned.
)


CREATE TABLE Interviews
(
	VacancyID varchar(5) foreign key references Vacances(VacancyID) not null,
	ApplicantID varchar(5) foreign key references Applicants(ApplicantID) not null ,
	EmpNo varchar(30) foreign key references Employees(EmpNo) not null,	
	ScheduleDate datetime,
	AttachedDate datetime,
	StartTime varchar(20),
	EndTime varchar(20),
	primary key(VacancyID,ApplicantID),
	InStatus int not null --Scheduled/Selected/Rejected/Not Required
)




